MYDB=$DB_GTRENDS
INPUT_FOLDER=../data/raw_data/GoogleTrends
INPUT_GEO_FOLDER=../data/raw_data/geography
OUTPUT_FOLDER=../data/data_csv

# Import Google trends (at region level, using old region definitions)
importCSVFile $MYDB $INPUT_FOLDER/GoogleTrendsAsColumns.csv Gtrends0

# Import old region definitions and merge to get data at DEP level
importCSVFile $MYDB $INPUT_GEO_FOLDER/depts2012.csv old_deps

equalJoin $MYDB 1 Gtrends1 Gtrends0 old_deps RegionId
selectColumns $MYDB 1 tmp_month Gtrends1 DEP yearMonth competitor_GoogleTrend
dropTables $MYDB Gtrends0 Gtrends1 old_deps

# Merge with mydays to get data at day level and at week level
equalJoin $MYDB 1 tmp_day tmp_month mydays yearMonth
equalJoin $MYDB 1 tmp_week tmp_month myweeks yearMonth

##### Merge with scope at all levels #####
sqlite3 $MYDB "create table GoogleTrends_CantonId_day as \
    select a.CantonId, a.dayNum, competitor_GoogleTrend \
    from Scope_CantonId_day a left join tmp_day b on a.DEP = b.DEP and a.dayNum = b.dayNum;"

sqlite3 $MYDB "create table GoogleTrends_CantonId_week as \
    select a.CantonId, a.weekNum, competitor_GoogleTrend \
    from Scope_CantonId_week a left join tmp_week b on a.DEP = b.DEP and a.weekNum = b.weekNum;"

### Clean up
dropTables $MYDB tmp_day tmp_week tmp_month
for TIME_LEVEL in week day ; do
    dropTables $MYDB Scope_CantonId_${TIME_LEVEL}
done
dropTables $MYDB mydays myweeks
sqlite3 $MYDB "VACUUM;"
